package org.alex.sample.dao.Impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.alex.sample.dao.SubjectDAO;
import org.alex.sample.model.Subject;
import org.alex.sample.model.User;
import org.alex.sample.page.PagerModel;
import org.alex.sample.page.SystemContext;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Transactional
@Repository
public class SubjectDAOImpl extends BaseHibernateDAO<Subject> implements SubjectDAO {

	@Override
	public List<Subject> findAllSubject() throws Exception {
		String hql = "FROM Subject";
		List<Subject> subjectList = this.find(hql);
		return subjectList;
	}

	@Override
	public void createSubject(Subject subject) throws Exception {
		this.save(subject);
	}

	@Override
	public void editSubject(Subject subject) throws Exception {
		this.update(subject);
	}

	@Override
	public void deleteSubject(Integer subId) throws Exception {
		this.deleteByKey(subId);
	}

	@Override
	public Subject findSubjectById(Integer subId) throws Exception {
		Subject subject = this.get(subId);
		return subject;
	}
	
	@SuppressWarnings("unchecked")
	public PagerModel<Subject> findAllByPage() throws Exception{
		PagerModel<Subject> page = new PagerModel<Subject>();
		int offset = SystemContext.getOffset(); //偏移量
		int pageSize = SystemContext.getPageSize(); //每页的记录数
		//使用hibernate完成分页
		List<Subject> tempDates = (List<Subject>) this.getSession().createQuery("from Subject")
				.setFirstResult(offset).setMaxResults(pageSize).list();
		page.setDates(tempDates);
		page.setOffset(offset);
		page.setPageSize(pageSize);
		
		//获取总记录数
		long totalSize = (long)this.getSession().createQuery("select count(*) from Subject").uniqueResult();
		page.setTotalSize(totalSize);
		return page;
	}

	//查找关联科目的学生
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getSubjectUser(Integer subjectId) throws Exception {
		StringBuilder sql = new StringBuilder();
		sql.append("select user.id as userId ");
		sql.append("from exam_user_subject eus ");
		sql.append("left join exam_subject as es on eus.sub_id = es.sub_id ");
		sql.append("left join exam_user as user on user.id = eus.user_id ");
		sql.append("where es.sub_id = :subjectId");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("subjectId", subjectId).list();
	}

	//添加关联科目新勾选的学生
	@Override
	public void insertSubjectUser(List<String> insertList, Integer subjectId) throws Exception {
		for(int i = 0; i < insertList.size(); i++) {
			StringBuilder sql = new StringBuilder();
			sql.append("insert into exam_user_subject(user_id,sub_id) value(");
			sql.append(insertList.get(i));
			sql.append(",");
			sql.append(subjectId);
			sql.append(")");
			this.createSqlQuery(String.valueOf(sql)).executeUpdate();
		}
	}

	//删除关联科目去掉勾选的学生
	@Override
	public void deleteSubjectUser(List<String> deleteList, Integer subjectId) throws Exception {
		if(deleteList.size() > 0) {
			StringBuilder sql = new StringBuilder();
			sql.append("delete from exam_user_subject where ");
			sql.append("user_id in(");
			if(deleteList.size() == 1) {
				sql.append(deleteList.get(0) + ") and sub_id=" + subjectId);
			}else {
				for(int i = 0; i < deleteList.size(); i++) {
					if(i == 0) {
						sql.append(deleteList.get(i));
					}else if(i == deleteList.size() - 1) {
						sql.append("," + deleteList.get(i) + ") and sub_id=" + subjectId);
					}else {
						sql.append("," + deleteList.get(i));
					}
				}
			}
			this.createSqlQuery(String.valueOf(sql)).executeUpdate();
		}
	}
	

	@Override
	public Iterator<Subject> iterate(String queryString, Object[] values) {
		// TODO Auto-generated method stub
		return null;
	}
}
